﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

using System.Drawing.Imaging;
using System.IO;

using System.Xml;
using System.Windows.Forms;
using System.Runtime.InteropServices;


namespace DBBase
{
    public class Query
    {

        public static string sDataBaseIp;
        public static string sDataBaseName;
        public static string sDataBaseUserName;
        public static string sDataBasePwd;
        public static string sjczbhzj;
        //公众号数据库链接
        public static string ssDataBaseIp;
        public static string ssDataBaseName;
        public static string ssDataBaseUserName;
        public static string ssDataBasePwd; 

        public static string getjczbhzj()
        {

         //   string path = "";
   

            INIfile iniex = new INIfile();
            iniex.path = Application.StartupPath + @"\Business.ini";
         
            sjczbhzj = iniex.IniReadValue("设置", "检测站编号");
            return sjczbhzj;

        }
        public void readxml()
        {
            
   
                INIfile iniex = new INIfile();
                iniex.path = Application.StartupPath + @"\Business.ini";
                sDataBaseIp = DecodeBase64("utf-8",iniex.IniReadValue("设置", "数据库IP"));
                sDataBaseName = DecodeBase64("utf-8",iniex.IniReadValue("设置", "数据库名称"));
                sDataBaseUserName = DecodeBase64("utf-8",iniex.IniReadValue("设置", "数据库用户名"));
                sDataBasePwd = DecodeBase64("utf-8",iniex.IniReadValue("设置", "数据库密码"));
           
        }
        ///编码
        public  string EncodeBase64(string code_type, string code)
        {
            string encode = "";
            byte[] bytes = Encoding.GetEncoding(code_type).GetBytes(code);
            try
            {
                encode = Convert.ToBase64String(bytes);
            }
            catch
            {
                encode = code;
            }
            return encode;
        }
        ///解码
        public  string DecodeBase64(string code_type, string code)
        {
            string decode = "";
            byte[] bytes = Convert.FromBase64String(code);
            try
            {
                decode = Encoding.GetEncoding(code_type).GetString(bytes);
            }
            catch
            {
                decode = code;
            }
            return decode;
        }
  

        public bool InsertDB(string sql)
        {
            readxml();
            string connectstr = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
            SqlConnection sqlcnn = new SqlConnection(connectstr);
            try
            {
                //打开数据库
                sqlcnn.Open();
                SqlCommand cmd = new SqlCommand(sql, sqlcnn);
                //执行插入操作，并返回受影响的行数
                int i = cmd.ExecuteNonQuery();
                //如果受影响的行数小于一则说明操作失败了
                sqlcnn.Close();
                if (i < 1)
                    return false;
                //关闭数据库
            }
            catch (Exception er)
            {
                sqlcnn.Close();
                string temp = er.Message.ToString();
                //MessageBox.Show(temp);
                return false;
            }



            return true;
        }
        public int selectDBcount(string sql)//查询该SQL语句的返回结果
        {
            readxml();
            int icount = 0;

            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection cnn = new SqlConnection(connectionstring);
                cnn.Open();
                SqlCommand cmd = new SqlCommand(sql, cnn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];//结果在这个table里，把它作为数据源就好了
                cnn.Close();
                icount = ds.Tables[0].Rows.Count;
                return icount;
            }
            //catch (Exception ex2)
            //{
            //    MessageBox.Show(ex2.ToString());
            //    return 0;
            //}

        }
        public string GetDBString(string sql, string DBname)
        {
            readxml();
            string m_stest;
            m_stest = "";
            try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection cnn = new SqlConnection(connectionstring);
                cnn.Open();
                SqlCommand cmd = new SqlCommand(sql, cnn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                System.Data.DataTable dt = ds.Tables[0];//结果在这个table里，把它作为数据源就好了
                foreach (DataRow myDataRow in dt.Rows)
                {

                    m_stest = myDataRow[DBname].ToString().Trim();
                }
                cnn.Close();

            }
            catch (Exception ex2)
            {
                MessageBox.Show("连接数据库失败，请检查数据库是否正常连接");
                return m_stest;

            }
            return m_stest;
        }
        public int GetDBInt(string sql, string DBname)
        {
            readxml();
            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection cnn = new SqlConnection(connectionstring);
                cnn.Open();
                SqlCommand cmd = new SqlCommand(sql, cnn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];//结果在这个table里，把它作为数据源就好了
                foreach (DataRow myDataRow in dt.Rows)
                {

                    if (myDataRow[DBname].ToString().Trim() != "")
                    {
                        return int.Parse(myDataRow[DBname].ToString().Trim());
                    }
                    else
                    {
                        return 0;
                    }
                }
                cnn.Close();

            }
            //catch (Exception ex2)
            //{
            //    MessageBox.Show(ex2.ToString());
            //    return 0;

            //}
            return 0;
        }
        public DataTable GetTable(string sSQL)
        {
            readxml();
            try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                sqlconn.Close();
                return ds.Tables[0];

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return new DataTable();
            }
        }
        public DataTable GetTableADB(string sSQL)
        {
            readxml();
            try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, "ADB", sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                sqlconn.Close();
                return ds.Tables[0];

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return new DataTable();
            }
        }

        public DataSet GetDataSet(string sSQL)
        {
            readxml();
            try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                //adapter.Fill(ds);



                //adapter.Fill(ds, "emp");
                //adapter.Fill(ds, "job");

                sqlconn.Close();
                return ds;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return new DataSet();
            }
        }
  
   
        public bool UpdateTable(string sSQL)
        {
            readxml();
            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                //string supdate=string.Format("update {0} set {1}={2} where {3}={4}"
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                //执行插入操作，并返回受影响的行数
                int i = cmd.ExecuteNonQuery();
                sqlconn.Close();
                if (i < 1)
                    return false;
                else
                {
                    return true;
                }
            }
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //    return false;
            //}
        }


        public DataTable GetTableAJLW(string sSQL)
        {
            readxml();
            //try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, "AJLW", sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                sqlconn.Close();
                return ds.Tables[0];

            }
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //    return new DataTable();
            //}
        }

        public bool InsertDBAJLW(string sql)
        {
            readxml();
            string connectstr = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, "AJLW", sDataBaseUserName, sDataBasePwd);
            SqlConnection sqlcnn = new SqlConnection(connectstr);
            //try
            {
                //打开数据库
                sqlcnn.Open();
                SqlCommand cmd = new SqlCommand(sql, sqlcnn);
                //执行插入操作，并返回受影响的行数
                int i = cmd.ExecuteNonQuery();
                //如果受影响的行数小于一则说明操作失败了
                if (i < 1)
                    return false;
                //关闭数据库
                sqlcnn.Close();
            }
            //catch (Exception er)
            //{
            //    string temp = er.Message.ToString();
            //    MessageBox.Show(temp);
            //    return false;
            //}
            return true;
        }

        public bool ExecuteS(string sSQL)
        {
            readxml();
           // try
            {
                string connectionstring = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", sDataBaseIp, sDataBaseName, sDataBaseUserName, sDataBasePwd);
                SqlConnection sqlconn = new SqlConnection(connectionstring);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(sSQL, sqlconn);
                int i = Convert.ToInt32(cmd.ExecuteScalar());
                sqlconn.Close();
                if (i < 1)
                    return false;
                else
                {
                    return true;
                }
            }
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //    return false;
            //}
        }
/// <summary>
/// MySQL数据ip读取
/// </summary>
        public void getgzip()
        {
            INIfile iniex = new INIfile();
            iniex.path = Application.StartupPath + @"\Business.ini";
            ssDataBaseIp = iniex.IniReadValue("设置", "公众号数据库IP");
            ssDataBaseName = iniex.IniReadValue("设置", "公众号数据库名称");
            ssDataBaseUserName = iniex.IniReadValue("设置", "公众号数据库用户名");
            ssDataBasePwd = iniex.IniReadValue("设置", "公众号数据库密码");
        }


    }

    //************************读取配置文件****************************
    public class INIfile
    {
        public string path;//配置文件的路径
        [DllImport("kernel32")]
        private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);
        [DllImport("kernel32")]
        private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retVal, int size, string filePath);
        [DllImport("kernel32")]
        private static extern int GetPrivateProfileString(string section, string key, string defVal, Byte[] retVal, int size, string filePath);
        public string IniReadValue(string section, string key)
        {
            StringBuilder temp = new StringBuilder(255);
            GetPrivateProfileString(section, key, "", temp, 255, this.path);
            return temp.ToString();
        }
        public void IniWriteValue(string section, string key, string val)
        {
            WritePrivateProfileString(section, key, val, path);
        }
    }

}
